/*	--- JDBC查询 ---

前面我们讲了Java程序要通过【JDBC接口】来查询【数据库】。

Q: 【JDBC】是一套【接口规范】, 它在哪呢？
A: 就在Java的标准库【java.sql.*;】里放着, 不过这里面大部分都是【接口】。接口并【不能】直接【实例化】, 而是【必须】实例化【对应的实现类】, 然后通过接口【引用】这个实例。


Q: 那么问题来了：【JDBC接口的实现类】在哪？
A: 因为【JDBC接口】并不知道我们要使用哪个数据库, 所以, 用【哪个数据库】, 我们就去使用【哪个数据库的“实现类”】

我们把 【某个数据库实现了JDBC接口的jar包】 称为 【JDBC驱动】。


因为我们选择了【MySQL 5.x】作为【数据库】, 所以我们首先得找一个【MySQL的JDBC驱动】。

所谓【JDBC驱动】, 其实就是一个【第三方jar包】, 我们直接添加【一个Maven依赖】就可以了：

	<dependency>
	    <groupId>mysql</groupId>
	    <artifactId>mysql-connector-java</artifactId>
	    <version>5.1.47</version>
	    <scope>runtime</scope>
	</dependency>



注意到这里添加【依赖的scope】是【runtime】, 因为【编译Java程序】并不需要【MySQL的这个jar包】, 只有在【运行期】才需要使用。

如果把【runtime】改成【compile】, 虽然也能正常编译, 但是在IDE里写程序的时候, 会多出来一大堆类似【com.mysql.jdbc.Connection这样的类】, 非常容易与【Java标准库的JDBC接口】混淆, 所以【坚决不要】设置为【compile】。

------------------------------


有了驱动, 我们还要确保【MySQL】在本机正常运行, 并且还需要【准备】一点【数据】。

这里我们用一个【脚本】创建【数据库和表】, 然后插入一些数据：

-- 创建数据库learn_jdbc
Drop DataBase If Exists learn_jdbc;
Create Database learn_jdbc;

-- 创建登录用户:learn / 口令:learnpassword
Create User If not exists learn@'%' Identified By 'learnpassword';
Grant All Privileges On learn_jdbc.* To learn@'%' With Grant Option;	
Flush Privileges;


-- 创建表students:
Use learn_jdbc;
Create Table students (
	id BigInt Auto_Increment Not Null,
	name Varchar(50) Not Null,
	gender TinyInt(1) Not Null,
	grade Int Not Null,
	score Int Not Null,
	Primary Key(id)
) Engine = Innodb Default Charset = UTF8;


-- 插入初始数据
Insert Into students(name, gender, grade, score) Values('小明', 1, 1, 88);
INSERT INTO students (name, gender, grade, score) VALUES ('小红', 1, 1, 95);
INSERT INTO students (name, gender, grade, score) VALUES ('小军', 0, 1, 93);
INSERT INTO students (name, gender, grade, score) VALUES ('小白', 0, 1, 100);
INSERT INTO students (name, gender, grade, score) VALUES ('小牛', 1, 2, 96);
INSERT INTO students (name, gender, grade, score) VALUES ('小兵', 1, 2, 99);
INSERT INTO students (name, gender, grade, score) VALUES ('小强', 0, 2, 86);
INSERT INTO students (name, gender, grade, score) VALUES ('小乔', 0, 2, 79);
INSERT INTO students (name, gender, grade, score) VALUES ('小青', 1, 3, 85);
INSERT INTO students (name, gender, grade, score) VALUES ('小王', 1, 3, 90);
INSERT INTO students (name, gender, grade, score) VALUES ('小林', 0, 3, 91);
INSERT INTO students (name, gender, grade, score) VALUES ('小贝', 0, 3, 77);



在控制台输入mysql -u root -p，输入root口令后以root身份，把上述SQL贴到控制台执行一遍就行。如果你运行的是最新版MySQL 8.x，需要调整一下CREATE USER语句。


=================================================


#	JDBC连接


使用JDBC时，我们先了解什么是Connection。

Connection代表一个JDBC连接，它相当于【Java程序】到【数据库】的连接。
(通常是【TCP连接】 )

打开一个Connection时，需要准备【URL、用户名和口令】，才能成功连接到【数据库】。

【URL】是由【数据库厂商指定的格式】，例如，MySQL的URL是：

jdbc:mysql://<hostname>:<port>/<db>?key1=value1&key2=value2
	

假设数据库运行在本机localhost，端口使用标准的3306，数据库名称是learnjdbc，那么URL如下：
jdbc:mysql://localhost:3306/learnjdbc?useSSL=false&characterEncoding=utf8


后面的两个参数表示不使用【SSL加密】，使用【UTF-8】作为字符编码（注意MySQL的UTF-8是utf8）。

要获取数据库连接，使用如下代码：*/
// JDBC连接的URL, 不同数据库有不同的格式
String JDBC_URL = "jdbc:mysql://localhost:3306/test";
String JDBC_USER = "root";
String JDBC_PASSWORD = "password";

// 获取连接
Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
// TODO: 访问数据库...
// 关闭连接
conn.close();


/*
核心代码是【DriverManager】提供的【静态方法getConnection()】。

【DriverManager】会自动扫描【classpath】，找到【所有的JDBC驱动】，然后根据我们【传入的URL】自动挑选一个【合适的驱动】。

因为【JDBC连接】是一种昂贵的资源，所以【使用后要及时释放】。

使用【try (resource)】来【自动】释放【JDBC连接】是一个好方法：*/
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
	//...
}



/*-----------------------------------------------------


#	JDBC查询


获取到【JDBC连接】后，下一步我们就可以【查询数据库】了。

------------------------

【查询数据库】分以下几步：

第1步，通过【Connection】提供的【createStatement()方法】创建一个【Statement对象】，用于执行一个查询；

第2步，执行【Statement对象】提供的【executeQuery("SELECT * FROM students");】并传入SQL语句，【执行查询】并获得【返回的结果集】，使用【ResultSet】来【引用】这个【结果集】；

第3步，反复调用【ResultSet的next()方法】来读取【每一行结果】。

-----------------


完整查询代码如下：*/
String sql_query = "Select * from stuents Where gender = 1";

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
	try (Statement stmt = conn.createStatement()) {
		try (ResultSet rs = stmt.executeQuery(sql_query)) {
			while (rs.next()) {
				long id = rs.getLong(1); // 索引从1开始
				long grade = rs.getLong(2); 
				String name = rs.getString(3);
				int gender = rs.getInt(4);
			}
		}
	}
}



/*
注意要点：

【Statment】和【ResultSet】都是【需要关闭的资源】，因此【嵌套使用try (resource) 】确保【及时关闭】；

【rs.next() 】用于判断【是否有下一行记录】，如果有，将自动把【当前行】移动到【下一行（一开始获得ResultSet时当前行不是第一行）】；

【ResultSet】获取【列】时，【索引】从【1】开始而不是0；

必须根据【SELECT的列】的【对应位置】来调用【getLong(1)，getString(2)这些方法】，否则【对应位置的数据类型】不对，将报错。



-----------------------------------------------


#	SQL注入


使用【Statement拼字符串】非常容易引发【SQL注入】的问题，这是因为【SQL参数】往往是从方法参数传入的。

我们来看一个例子：假设【用户登录的验证方法】如下：*/
User login(String name, String pass) {
	//...
	stmt.executeQuery("Select * from user Where login = '" + name +'"' And pass = '"' + pass + "'");
	//...
}



/*
其中，参数【name】和【pass】通常都是【Web页面输入】后由【程序接收到】的。

如果用户的输入是程序期待的值，就可以拼出正确的SQL。例如：name = "bob"，pass = "1234"：

SELECT * FROM user WHERE login='bob' AND pass='1234'


但是，如果用户的输入是一个精心构造的字符串，就可以拼出意想不到的SQL，这个SQL也是正确的，但它查询的条件不是程序设计的意图。例如：name = "bob' OR pass=", pass = " OR pass='"：

SELECT * FROM user WHERE login='bob' OR pass=' AND pass=' OR pass=''

-------------------------


这个【SQL语句】执行的时候，根本不用判断【口令】是否正确，这样一来，登录就形同虚设。


要避免【SQL注入攻击】，一个办法是针对【所有字符串参数进行转义】，但是【转义】 很麻烦，而且需要在【任何使用SQL的地方】增加【转义代码】。


还有一个办法就是【使用PreparedStatement】。

使用【PreparedStatement】可以完全避免【SQL注入的问题】，因为【PreparedStatement】始终使用【?】作为【占位符】，并且把【数据】连同【SQL本身】传给【数据库】，这样可以保证【每次传给数据库的SQL语句】是相同的，只是【占位符的数据】不同，还能高效利用【数据库本身对查询的缓存】。


上述【登录SQL】如果用【PreparedStatement】可以改写如下：*/
User login(Sting name, String pass) {
	//...
	String sql = "Select * from user Where login = ? And pass = ?";

	PreparedStatement ps = conn.prepareStatement(sql);
	ps.setObject(1, name);
	ps.setObject(2, pass);
	//...
}


/*  所以，【PreparedStatement】比【Statement】更安全，而且更快。

！  使用【Java】对【数据库】进行操作时，必须使用【PreparedStatement】，【严禁】任何【通过参数拼字符串】的代码！


-----------------------------------------------


我们把上面使用Statement的代码改为使用PreparedStatement：*/
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
	try (PreparedStatement ps = conn.prepareStatement("Select id, grade, name, gender From students Where gender = ? And grade = ?")) {

		ps.setObject(1, "M"); // ps: 索引从1开始
		ps.setObject(2, 3);

		try (ResultSet rs = ps.executeQuery()) {
			while (rs.next()) {
				long id = rs.getLong("id");
				long grade = rs.getLong("grade");
				String name = rs.getString("name");
				String gender = rs.getString("gender");
			}
		}
	}
}



/*
使用【PreparedStatement】和【Statement】稍有不同，必须首先调用【setObject()】设置【每个占位符?的值】，最后获取的仍然是【ResultSet对象】。

另外注意到从【结果集】读取【列】时，使用【String类型的列名】比【索引】要易读，而且不易出错。

-------------------------


注意到【JDBC查询的返回值】总是【ResultSet】，即使我们写这样的【聚合查询Select SUM(score) FROM ...，】也需要按【结果集】读取：*/
ResultSet rs = ...;

if (rs.next()) {
	double sum = rs.getDouble(1);
}



/*-----------------------------------------------


#	数据类型


有的童鞋可能注意到了，使用【JDBC】的时候，我们需要在【Java数据类型】和【SQL数据类型】之间进行【转换】。

【JDBC】在【java.sql.Types】定义了【一组常量】来表示【如何映射SQL数据类型】，但是平时我们使用的类型通常也就以下几种：


SQL数据类型		Java数据类型
-----------------------------------
BIT, BOOL		boolean
INTEGER			int
BIGINT			long
REAL			float
FLOAT, DOUBLE	double
CHAR, VARCHAR	String
DECIMAL			BigDecimal
DATE			java.sql.Date, LocalDate
TIME			java.sql.Time, LocalTime


注意：只有最新的JDBC驱动才支持LocalDate和LocalTime。



===================================================


#	----- JDBC查询 の 小结 ----- 


1. JDBC接口的Connection代表一个JDBC连接；

2. 使用JDBC查询时，总是使用PreparedStatement进行查询而不是Statement；

3. 【查询结果】总是【ResultSet】，即使使用【聚合查询】也不例外。

	聚合查询(聚合的计算):
		SELECT COUNT(*) num FROM students;

	COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。

------------

标准模板：*/
import java.sql.*;

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
	try (PreparedStatement ps = conn.preparedStatement("Select id, grade, name, gender From students Where gender = ? And grade = ?")) {

		ps.setObject(1, "M"); // ps: 索引从1开始
		ps.setObject(2, 3);

		try (ResultSet rs = ps.executeQuery()) {
			while (rs.next()) {
				long id = rs.getLong("id");
				long grade = rs.getLong("grade");
				String name = rs.getString("name");
				String gender = rs.getString("gender");
			}
		}
	}
}




